<?xml version="1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title>twisted.enterprise.adbapi: Twisted RDBMS support</title>
  </head>

  <body>
    <h1>twisted.enterprise.adbapi: Twisted RDBMS support</h1>

    <h2>Abstract</h2>

    <p>Twisted is an asynchronous networking framework, but most
    database API implementations unfortunately have blocking
    interfaces -- for this reason, <code
    class="API">twisted.enterprise.adbapi</code> was created. It is
    a non-blocking interface to the standardized DB-API 2.0 API,
    which allows you to access a number of different RDBMSes.</p>

    <h2>What you should already know</h2>

    <ul>
      <li>Python :-)</li>

      <li>How to write a simple Twisted Server (see <a
      href="servers.xhtml">this tutorial</a> to learn how)</li>

      <li>Familiarity with using database interfaces (see <a
      href="http://www.python.org/dev/peps/pep-0249/">
      the documentation for DBAPI 2.0</a> or this <a
      href="http://www.amk.ca/python/writing/DB-API.html">article</a>
      by Andrew Kuchling)</li>
    </ul>

    <h2>Quick Overview</h2>

    <p>Twisted is an asynchronous framework. This means standard
    database modules cannot be used directly, as they typically
    work something like:</p>
<pre class="python">
# Create connection... 
db = dbmodule.connect('mydb', 'andrew', 'password') 
# ...which blocks for an unknown amount of time 
 
# Create a cursor 
cursor = db.cursor() 
 
# Do a query... 
resultset = cursor.query('SELECT * FROM table WHERE ...') 
# ...which could take a long time, perhaps even minutes. 
</pre>

    <p>Those delays are unacceptable when using an asynchronous
    framework such as Twisted. For this reason, twisted provides
    <code class="API">twisted.enterprise.adbapi</code>, an
    asynchronous wrapper for any <a
    href="http://www.python.org/dev/peps/pep-0249/">
    DB-API 2.0</a>-compliant module.</p>

    <p><code base="twisted" class="API">enterprise.adbapi</code> will do
    blocking
    database operations in separate threads, which trigger
    callbacks in the originating thread when they complete. In the
    meantime, the original thread can continue doing normal work,
    like servicing other requests.</p>

    <h2>How do I use adbapi?</h2>

    <p>Rather than creating a database connection directly, use the
    <code base="twisted.enterprise" class="API">adbapi.ConnectionPool</code>
    class to manage
    a connections for you. This allows <code
    base="twisted" class="API">enterprise.adbapi</code> to use multiple
    connections, one per thread. This is easy:</p>
<pre class="python">
# Using the "dbmodule" from the previous example, create a ConnectionPool 
from twisted.enterprise import adbapi 
dbpool = adbapi.ConnectionPool("dbmodule", 'mydb', 'andrew', 'password') 
</pre>

    <p>Things to note about doing this:</p>

    <ul>
      <li>There is no need to import dbmodule directly. You just
      pass the name to <code base="twisted.enterprise"
      class="API">adbapi.ConnectionPool</code>'s constructor.</li>

      <li>The parameters you would pass to dbmodule.connect are
      passed as extra arguments to <code base="twisted.enterprise"
      class="API">adbapi.ConnectionPool</code>'s constructor.
      Keyword parameters work as well.</li>
    </ul>

   <p>Now we can do a database query:</p>

<pre class="python">
# equivalent of cursor.execute(statement), return cursor.fetchall():
def getAge(user):
    return dbpool.runQuery("SELECT age FROM users WHERE name = ?", user)

def printResult(l):
    if l:
        print l[0][0], "years old"
    else:
        print "No such user"

getAge("joe").addCallback(printResult)
</pre>

    <p>This is straightforward, except perhaps for the return value
    of <code>getAge</code>. It returns a <code
    class="API">twisted.internet.defer.Deferred</code>, which allows
    arbitrary callbacks to be called upon completion (or upon
    failure).  More documentation on Deferred is available <a
    href="defer.xhtml">here</a>.</p>

    <p>In addition to <code>runQuery</code>, there is also <code>runOperation</code>,
    and <code>runInteraction</code> that gets called with a callable (e.g. a function).
    The function will be called in the thread with a <code class="API">twisted.enterprise.adbapi.Transaction</code>,
    which basically mimics a DB-API cursor. In all cases a database transaction will be 
    commited after your database usage is finished, unless an exception is raised in
    which case it will be rolled back.</p>

<pre class="python">
def _getAge(txn, user):
    # this will run in a thread, we can use blocking calls
    txn.execute("SELECT * FROM foo")
    # ... other cursor commands called on txn ...
    txn.execute("SELECT age FROM users WHERE name = ?", user)
    result = txn.fetchall()
    if result:
        return result[0][0]
    else:
        return None

def getAge(user):
    return dbpool.runInteraction(_getAge, user)

def printResult(age):
    if age != None:
        print age, "years old"
    else:
        print "No such user"

getAge("joe").addCallback(printResult)
</pre>

    <p>Also worth noting is that these examples assumes that dbmodule
    uses the <q>qmarks</q> paramstyle (see the DB-API specification). If
    your dbmodule uses a different paramstyle (e.g. pyformat) then
    use that. Twisted doesn't attempt to offer any sort of magic
    paramater munging -- <code class="python">runQuery(query,
    params, ...)</code> maps directly onto <code
    class="python">cursor.execute(query, params, ...)</code>.</p>

	<h2>Examples of various database adapters</h2>

	<p>Notice that the first argument is the module name you would
	usually import and get <code class="python">connect(...)</code>
	from, and that following arguments are whatever arguments you'd
	call <code class="python">connect(...)</code> with.</p>
	 
<pre class="python">
from twisted.enterprise import adbapi

# Gadfly
cp = adbapi.ConnectionPool("gadfly", "test", "/tmp/gadflyDB")

# PostgreSQL PyPgSQL
cp = adbapi.ConnectionPool("pyPgSQL.PgSQL", database="test")

# MySQL
cp = adbapi.ConnectionPool("MySQLdb", db="test")
</pre>

    <h2>And that's it!</h2>

    <p>That's all you need to know to use a database from within
    Twisted. You probably should read the adbapi module's
    documentation to get an idea of the other functions it has, but
    hopefully this document presents the core ideas.</p>
  </body>
</html>

